Processing Data

After you've successfully opened a connection, your next step will probably be to read some records from the data source. You can accomplish this in several ways, but all of them involve the creation of a Recordset object.

Before you open a Recordset, you must decide which records you want to retrieve, which type of cursor you want to create (if any), the cursor's location, and so on.

The most important property of a Recordset object is its Source property, which indicates which records should be retrieved. This property can be the name of a database table or view, the name of a stored procedure, or the text of a SELECT command. When you're working with file-based Recordsets, the Source property can also be the name and path of a file. (File-based Recordsets are described later in this chapter.) Here are a few examples:

' Select a different source, based on an array of option buttons. Dim rs As New ADODB.Recordset If optSource(0).Value Then ' Database table rs.Source = "Authors" ElseIf optSource(1).Value Then ' Stored procedure rs.Source = "reptq1" ElseIf optSource(2) Then ' SQL query rs.Source = "SELECT * FROM Authors" WHERE au_lname LIKE 'A*'" End If

When you open a Recordset, you must specify the connection that you want used. You can do this in at least four ways:

• You create a stand-alone Connection object with all the properties you want (connection timeout, user name and password, and so on), you open it, and then you assign it to the Recordset's ActiveConnection property before opening the Recordset.
• You create a stand-alone Connection object as described in the previous point and pass it as the second argument of the Recordset's Open method. The effects of this sequence are identical, but it lets you save one statement.
• You pass a connection string as the second argument of the Recordset's Open method. In this scenario, ADO creates an implicit Connection object that you can later access through the Recordset's ActiveConnection property. This method is the most concise of the four, in that it requires only one executable statement.
• You create a stand-alone Connection object as shown in the first two points. Then you pass the source string as the first argument to its Execute method and assign the returned value to a Recordset variable. This means of specifying a connection is the least flexible of the group because you have little control over the type of the returned Recordset.

I'll describe a few other ways to open a Recordset, based on the Command object, in the "Using Command Objects" section, later in this chapter. Here are some code examples, all of which open the Authors table of the Pubs database on the SQL Server named P2:

' Method 1: explicit Connection assigned to the ActiveConnection property. Dim cn As New ADODB.Connection, rs As New ADODB.Recordset cn.ConnectionTimeout = 5 cn.Open "Provider=sqloledb;Data Source=P2;Initial Catalog=pubs;", "sa" Set rs.ActiveConnection = cn rs.Open "Authors" ' Method 2: explicit Connection passed to the Open method. Dim cn As New ADODB.Connection, rs As New ADODB.Recordset cn.ConnectionTimeout = 5 cn.Open "Provider=sqloledb;Data Source=P2;Initial Catalog=pubs;", "sa" rs.Open "Authors", cn ' Method 3: implicit Connection created in the Recordset's Open method. ' Note that you need to embed additional connection attributes (such as ' connection timeout and user ID) in the connection string. Dim rs As New ADODB.Recordset rs.Open "Authors", "Provider=sqloledb;Data Source=P2;" _ & "Initial Catalog=pubs;User ID=sa;Connection Timeout=10" ' Method 4: the Execute method of the Connection object. By default, it ' opens a server-side forward-only, read-only Recordset with CacheSize=1. Dim cn As New ADODB.Connection, rs As New ADODB.Recordset cn.Open "Provider=sqloledb;Data Source=P2;Initial Catalog=pubs;", "sa" Set rs = cn.Execute("Authors")

Notice a substantial difference among all these approaches: The first, the second, and the fourth methods let you easily share the same connection among multiple Recordsets, whereas if you open multiple Recordsets using the third method each Recordset would use a different connection even if you use the same connection string for all of them.

You can pass many types of strings to the Open method or the Source property and let ADO determine what they represent. This has a price, however, because you force ADO to send one or more queries to the database just to find out whether the source string is the name of a table, a view, a stored procedure, or the text of an SQL command. You can avoid these additional trips to the server by assigning a correct value to the last argument of the Open method, as in the following examples:

' Select a different source, based on an array of option buttons. If optSource(0).Value Then ' Database table rs.Open "Publishers", , , , adCmdTable Else optSource(1).Value Then ' Stored procedure rs.Open "reptq1", , , , adCmdStoredProc ElseIf optSource(2) Then ' SQL query rs.Open "SELECT * FROM Authors", , , , adCmdText End If

Recordsets can greatly differ in functionality and performance. For example, a Recordset can be updatable or read-only; it can support only the MoveNext command or be fully scrollable. Another key difference is in whether the Recordset contains the actual data or is just a collection of bookmarks that are used to retrieve the data from the database when necessary. It goes without saying that a client-side Recordset based on bookmarks takes fewer resources in the client application but might generate more network traffic when new data needs to be retrieved. Incidentally, this makes it almost impossible to compare the performance of different data retrieval techniques because they depend on too many factors.

The kinds of operations supported by a Recordset heavily depend upon the cursor on which the Recordset is based. Cursors are a collection of records that can be stored and maintained by the server database or by the client application. As you know from Chapter 13, ADO supports four types of cursors: forward-only read-only, static, keyset, and dynamic.

Cursors aren't very popular among professional programmers because of their appetite for resources and CPU time. Moreover, cursors often use locks on the database, which further reduces their scalability. Most heavy-duty client/server applications rely on cursorless Recordsets for retrieving data, and then update and insert records using SQL commands or, even better, stored procedures.

So, what are cursors good for? For one, when you're retrieving small sets of data—some hundreds of records, for example—a cursor is a reasonable choice. Cursors are also necessary when you want to enable your users to browse data and scroll back and forth through it, and you must use a cursor when your user interface is based on bound controls. In some cases, you're more or less forced to use cursors (in particular, client-side cursors) because a few interesting ADO features are available only with them. For example, persistent file-based Recordsets and hierarchical Recordsets can be based only on client-side static cursors, and you can use the Sort method only on this type of Recordset.

If you decide that cursors meets your requirements, you should at least attempt to reduce their overhead, which you do by adopting some simple but effective techniques. First, reduce the number of records in the cursor using an appropriate WHERE clause, and consider using the MaxRecords property to avoid huge cursors. Second, move to the last row of the Recordset as soon as possible, in order to free the locks on the data pages and index pages on the server. Third, always set the Recordset's CursorLocation, CursorType, and LockType properties so that the cursor isn't more powerful (and therefore less efficient) than what you actually need.

Speaking of CursorType and LockType, you should remember from Chapter 13 that you can also set these properties by passing values to the third and fourth arguments of the Open method, as the following code demonstrates:

' Open a server-side dynamic cursor. ' (Assumes that the ActiveConnection property has been set already.) rs.CursorType = adOpenDynamic rs.Open "SELECT * FROM Authors", , , , adCmdText ' Open a server-side keyset cursor, with a single statement. rs.Open "SELECT * FROM Authors", , adOpenKyset, adLockOptimistic, adCmdText

You can create client-side static cursors by simply setting the CursorLocation to adUseClient before opening the Recordset. This property, in fact, seems to have a higher priority than CursorType: Whatever cursor type you specify in the latter property or as an argument to the Open method, ADO always creates a Recordset based on a static cursor, which is the only cursor available on the client side:

' Open a client-side static cursor. rs.CursorLocation = adUseClient rs.CursorType = adOpenStatic ' This statement is optional. rs.Open "SELECT * FROM Authors", , , , adCmdText

Client-side static cursors offer a decent scalability because they use resources from each client and not from the server. The only resource used on the server is the open connection, but later in this chapter I'll show how you can work around this issue, using dissociated Recordsets and optimistic batch updates.

Server-side cursors have their advantages. They let you use less powerful client workstations and offer more choices in terms of cursor types and locking options. For example, a keyset or dynamic cursor can reside only on the server, server-side static cursors can be read-write, and client-side static cursors can only be read-only or use optimistic batch updates. Another point in favor of cursors is that SQL Server lets you have multiple active statements on a connection only if you're using serverside cursors. On the other hand, server-side cursors drain resources from the server, so scalability is often an issue. Each cursor you create on the server uses room in the TempDB database, so you must ensure that TempDB can accommodate all the cursors requested by client applications. Finally, server-side cursors usually generate high network traffic because each time the client needs a different record, a round-trip to the server is performed.

ADO Recordset objects are much more flexible than DAOs and RDOs, in that you don't even need an open connection to create a Recordset. In fact, ADO provides support for two different types of Recordsets: stand-alone Recordsets created from scratch and file-based Recordsets.

Stand-alone Recordsets are conceptually simple. You create a new Recordset object, append one or more fields to its Fields collection, and finally open it. What you get is a client-side Recordset based on a static cursor and optimistic batch locking:

' Creates a dissociated Recordset with three fields Dim rs As New ADODB.Recordset rs.Fields.Append "FirstName", adChar, 40, adFldIsNullable rs.Fields.Append "LastName", adChar, 40, adFldIsNullable rs.Fields.Append "BirthDate", adDate rs.Open

After you have opened the Recordset, you can add records to it and you can even assign it to the Recordset property of an ADO Data control or to the DataSource property of any bound control. This lets you bind a control to data of any type, even if it isn't stored in a database. For example, you can display the contents of a semicolon-delimited text file in a DataGrid control, as shown in Figure 14-1, by using the following code. (See on the companion CD for the complete application.)

Dim rs As New ADODB.Recordset Dim lines() As String, fields() As String Dim i As Long, j As Long ' Open the Publishers.dat text file. Open "Publishers.dat" For Input As #1 ' Read the contents of the file, and process each individual line. lines() = Split(Input(LOF(1), 1), vbCrLf) Close #1 ' Process the first line, which contains the list of fields. fields() = Split(lines(0), ";") For j = 0 To UBound(fields) rs.fields.Append fields(j), adChar, 200 Next rs.Open ' Process all the remaining lines. For i = 1 To UBound(lines) rs.AddNew fields() = Split(lines(i), ";") For j = 0 To UBound(fields) rs(j) = fields(j) Next Next ' Display the recordset in the DataGrid control. rs.MoveFirst Set DataGrid1.DataSource = rs

Using similar code, you can display the contents of a two-dimensional array of strings, of an array of User Defined Type structures, or even less traditional data sources, such as information coming from the serial port or an HTML page downloaded from the Internet.

ADO also supports saving the contents of a client-side Recordset to a disk file. This capability can greatly increase the functionality and the performance of your applications. For example, you can create local copies of small lookup tables and update them only when necessary. Or you can save a Recordset to a directory and let another program create a report out of it, possibly during off-hours. Or you can enable your users to save the current state of the application—including any Recordset being processed—and restore it in a later session. I have described file-based Recordset in detail in the "Implementing Persistent Recordsets" section in Chapter 13.

Figure 14-1. You can bind data-aware controls to any type of data, using stand-alone Recordsets.

Basic Operations on a Database

The ultimate purpose in connecting to a database is to read the data it contains and possibly modify it. As you'll see in a moment, ADO offers several ways to perform these tasks.

After you've created a Recordset, reading the data in it is as simple as iterating on all its records using a Do…Loop structure similar to the following one:

' Fill a list box with the names of all the authors. Dim rs As New ADODB.Recordset rs.Open "Authors", "Provider=sqloledb;Data Source=P2;" _ & "Initial Catalog=pubs;User ID=sa;Connection Timeout=10" Do Until rs.EOF lstAuthors.AddItem rs("au_fname") & " " & rs("au_lname") rs.MoveNext Loop rs.Close

The previous code works regardless of the type of Recordset you're working with, because all Recordsets—including cursorless ones—support the MoveNext method. You can reference the values in the current record using the more verbose syntax:

rs.Fields("au_fname").Value

but in most cases you'll omit both Fields (the default property for the Recordset object) and Value (the default property for the Field object) and use the most concise form:

rs("au_fname")

Reading the Value property of a Field object might fail if the field is a large binary object (BLOB), such as an image or a long memo stored in a database field. In this situation, you should retrieve the value using the Field object's GetChunk method, as described in the "The Field Object" section in Chapter 13. Similarly, you should write data to a BLOB field using the AppendChunk method.

ADO supports two other ways to retrieve data from an open Recordset. The first one is based on the GetRows method, which returns a Variant containing a two-dimensional array of values. The second one is based on the GetString method, which returns a long string where fields and records are separated using the characters you specify. In general, these methods are much faster than using a loop based on the MoveNext method, even though the actual speed improvement depends on many factors, including the type of cursor and the system memory available on the client machine. You can find a description of these methods in the "Retrieving Data" section of Chapter 13.

Provided that the Recordset is updatable, you can insert new records using the Recordset's AddNew method. Use the Supports method to determine whether you can add new records to the Recordset:

If rs.Supports(adAddNew) Then. . .

I've shown how you can use the AddNew method to add records to a stand-alone Recordset, and the same technique applies to a regular Recordset. If you've grown up with DAO and RDO, you might find ADO's AddNew method disconcerting at first because it doesn't require that you confirm the addition of the new record. In fact, any operation that moves the record pointer to another record—including another AddNew method—confirms the insertion of the new record. If you want to cancel the operation, you must call the CancelUpdate method, as this code demonstrates:

rs.AddNew rs.Fields("Name") = "MSPress" rs.Fields("City") = "Seattle" rs.Fields("State") = "WA" If MsgBox("Do you want to confirm?", vbYesNo) = vbYes Then rs.Update Else rs.CancelUpdate End If

Remember that you can't close a Recordset if an AddNew method hasn't been resolved with an Update method (implicit or explicit) or a CancelUpdate method.

Another feature of the AddNew method that's missing in DAO and RDO is its ability to pass an array of field names and values. To give you an idea of which sort of speed improvement you can get using this feature, I rewrote the loop that adds new records in the code snippet shown in the "Stand-Alone Recordset Objects" section, earlier in this chapter:

' Build the FieldNames() variant array. (You need to do this only once.) ReDim fieldNames(0 To fieldMax) As Variant For j = 0 To fieldMax fieldNames(j) = fields(j) Next ' Process the text lines, but use an array of values in AddNew. For i = 1 To UBound(lines) fields() = Split(lines(i), ";") ReDim fieldValues(0 To fieldMax) As Variant For j = 0 To UBound(fields) fieldValues(j) = fields(j) ' Move values into the Variant arrays. Next rs.AddNew fieldNames(), fieldValues() Next

While the amount of code is more or less the same, passing arrays of field names and values to the AddNew method makes this code run about three times faster than the original loop. This gives you an idea of the overhead you incur whenever you reference an item in the Fields collection.

ADO lets you modify field values in the current record without explicitly entering edit mode. In fact, unlike DAO and RDO, ADO Recordset objects expose no Edit method and you implicitly enter edit mode when you modify a field's value:

' Increment unit prices of all products by 10%. Do Until rs.EOF rs("UnitPrice") = rs("UnitPrice") * 1.1 rs.MoveNext Loop

If you aren't sure whether ADO has initiated an edit operation, you can query the EditMode property:

If rs.EditMode = adEditInProgress Then. . .

The Update method is similar to AddNew in that it also supports a list of field names and values. This feature is especially convenient when the same subset of values must be inserted in multiple records. Don't forget that the Update method might not be supported by the Recordset, depending on its type, location, and concurrency option. When in doubt, use the Supports method:

If rs.Supports(adUpdate) Then. . .

The syntax of the Delete method is simple: Depending on the argument you pass to this method, you can either delete the current record (the default) or all the records that are currently visible because of an active Filter property. In most cases, you'll use the default option. Remember that after you invoke this method, the current record becomes invalid, so you should move the record pointer to point to a valid record immediately after the delete operation:

rs.Delete rs.MoveNext If rs.EOF The rs.MoveLast

Even if the Recordset is updatable, you can't be sure that the Update method will succeed. In fact, an updatable record might be (possibly temporarily) made nonupdatable because it's being edited by another user. This is an issue only when you open the Recordset using a locking option other than the adLockReadOnly value. If you open a Recordset in read-only mode, you're not using locks at all and can happily go back and forth through your Recordset (only forth, if not scrollable) without caring about locking.

Different users can access the same set of records using different lock options. For example, user A might use pessimistic locking and user B might use optimistic locking. In this case, user A might lock the record even if user B is already editing it, in which case user B will be locked out until user A completes the update operation. If you use pessimistic locking, you should trap errors only when you start editing the record, whereas if you use optimistic locking you should trap errors only when you implicitly or explicitly update the record. If you're using optimistic batch locking, you must solve update conflicts, as I explain later in this chapter.

When you're using pessimistic locking and your edit operation fails, you get an error &H80004005, "Couldn't update; currently locked by user <username> on machine <machinename>." You get the same error when the Update command fails for a Recordset opened with optimistic locking. In both cases, you should implement a strategy for solving these locking problems: Typically you can either retry automatically after a while or notify the user that the edit or update operation failed and let him or her decide whether the command should be attempted again:

' Update strategy for optimistic locking. On Error Resume Next Do Err.Clear rs.Update If Err = 0 Then Exit Do ElseIf MsgBox("Update command failed:" & vbCr & Err.Description, _ vbRetryCancel + vbCritical) = vbCancel Then Exit Do End If Loop

Many database engines—including Microsoft Jet and SQL Server 6.5 and earlier—don't support locking at the record level and use locks that affect entire pages that can contain multiple records. (For example, Microsoft Jet supports 2-KB pages.) This means that a record can be locked even if it isn't being updated by a user but merely because a user has locked another record in the same page. Microsoft SQL Server 7 and Oracle databases support record-level locking. The locking mechanism also works on pages of indexes, so you might be prevented from updating a record because another user has locked the index page that contains a pointer to the record you're working with.

As you know, the most efficient Recordsets are those built on forward-only, read-only noncursors, which are nonupdatable Recordsets. Even if you opt for other types of cursors, for a better scalability I advise you to open the Recordset in read-only mode, which avoids locking and delivers applications that scale better. You must, however, implement a strategy for adding, inserting, and deleting records if such operations are needed. If the Recordset isn't updatable, your only choice is to send an SQL command to the database or to execute a stored procedure that you've created previously. In this section, I show you how to use plain SQL commands without parameters. The concepts, by the way, can be applied to other circumstances as well, which you'll see when I describe parameterized queries in the "Parameterized Commands and Queries" section, later in this chapter.

If you're working with a read-only Recordset, you can update an individual row using an UPDATE command as long as you can uniquely identify the current record. Usually you do that using the value of the primary key in the WHERE clause:

' Ask the end user for a new price for each product that costs more ' than $40. Dim rs As New ADODB.Recordset, cn As New ADODB.Connection Dim newValue As String cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=C:\Program Files\Microsoft Visual Studio\VB98\NWind.mdb" rs.Open "Products", cn Do Until rs.EOF If rs("UnitPrice") > 40 Then ' In a real-world application, you will surely use a better UI. newValue = InputBox("Insert a new price for product " & _ rs("ProductName"), , rs("UnitPrice")) If Len(newValue) Then cn.Execute "UPDATE Products SET UnitPrice=" & newValue & _ " WHERE ProductID =" & rs("ProductID") End If End If rs.MoveNext Loop

Deleting a record using an SQL command is similar, but you have to use the DELETE command instead:

' Ask users if they want to selectively delete suppliers from Italy. Dim rs As New ADODB.Recordset, cn As New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=E:\Microsoft Visual Studio\VB98\NWind.mdb" rs.Open "Suppliers", cn Do Until rs.EOF If rs("Country") = "Italy" Then If MsgBox("Do you want to delete supplier " & rs("Company Name") _ & "?", vbYesNo) = vbYes Then cn.Execute "DELETE FROM Suppliers WHERE SupplierID =" _ & rs("SupplierID") End If End If rs.MoveNext Loop

Update and delete operations can fail for several reasons, so you should always protect them from unanticipated errors. For example, the previous DELETE command fails if the deleted record is referenced by any record in the Products table, unless a cascading delete relationship has been established between the two tables.

Adding new records requires an INSERT INTO command:

cn.Execute "INSERT INTO Employees (LastName, FirstName, BirthDate) " _ & "VALUES ('Smith', 'Robert', '2/12/1953')"

When you retrieve the values from controls, you must build the SQL string programmatically, as in this code:

cn.Execute "INSERT INTO Employees (LastName, FirstName, BirthDate) " _ & "VALUES ('" & txtLastName & "', '" & txtFirstName _ & "', '" & txtBirthDate & "')"

You can write less code and make it more readable by defining a routine that replaces all the placeholders in a string:

' Replace all @n arguments with provided values. Function ReplaceParams(ByVal text As String, ParamArray args() As Variant) Dim i As Integer For i = LBound(args) To UBound(args) text = Replace(text, "@" & Trim$(i + 1), args(i)) Next ReplaceParams = text End Function

Here's how you can rewrite the previous INSERT command using the ReplaceParams routine:

sql = "INSERT INTO Employees (LastName, FirstName, BirthDate) " _ & "VALUES ('@1', '@2', '@3') cn.Execute ReplaceParams(sql, txtLastName, txtFirstName, txtBirthDate)

Until now, I haven't described in detail how optimistic batch updates work for a reason: They require a completely different programming logic and deserve a section of their own.

In a nutshell, ADO lets you create Recordsets on which you can perform all the commands you want to—including deletes, inserts, and updates—without immediately affecting the original rows in the database. You can even disconnect the Recordset from the database by setting its ActiveConnection property to Nothing and optionally close the companion Connection object. When you're finally ready to confirm the updates to the database, you simply have to reconnect the Recordset and issue an UpdateBatch command. Or you can use the CancelBatch method to cancel the pending changes. The following snippet is similar to a code example you saw in the previous section, but it uses optimistic batch updates instead of UPDATE SQL commands:

Dim rs As New ADODB.Recordset, cn As New ADODB.Connection ' Open the recordset with optimistic batch locking. cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=C:\Microsoft Visual Studio\VB98\NWind.mdb" cn.Open rs.CursorLocation = adUseClient rs.Open "Products", cn, adOpenStatic, adLockBatchOptimistic ' Disconnect the recordset from the data source and free the connection. Set rs.ActiveConnection = Nothing cn.Close Do Until rs.EOF If rs("UnitPrice") > 40 Then ' In a real-world application, you'll surely use a better UI. newValue = InputBox("Insert a new price for product " & _ rs("ProductName"), , rs("UnitPrice")) If Len(newValue) Then rs("UnitPrice") = newValue End If rs.MoveNext Loop ' Ask for confirmation of all changes. If MsgBox("Send updates to the database?", vbYesNo) = vbYes Then ' Reestablish the connection, and send the updates. cn.Open Set rs.ActiveConnection = cn rs.UpdateBatch Else rs.CancelBatch End If

Notice how the program closes the connection while it isn't being used by the Recordset and reopens it only if necessary. This detail can probably improve the scalability of your application more than any other technique you've seen so far.

The previous code example omits an essential part of any optimistic batch update routine—conflict handling. In fact, the optimistic in the name means that you hope that no other user has updated the same record while you were processing the Recordset locally. In practice, you must always trap errors and resolve any conflicts manually. These conflicts can occur because the records you've updated have been deleted by another user in the meantime, or because the fields that you've updated have been updated by another user. By default, ADO signals a conflict only if two users modify the same field, not when they modify different fields in the same record. For the best results, you should ensure that the table being updated has a primary key. Otherwise, you might accidentally update more records than you meant to.

To see which records caused the conflicts, you set the Filter property to the value adFilterConflictingRecords and then loop on the Recordsets, testing each record's Status property:

' A skeletal code that resolves batch update conflicts On Error Resume Next rs.UpdateBatch rs.Filter = adFilterConflictingRecords If rs.RecordCount > 0 Then ' Resolve the conflicts here. End If ' Go back to the regular recordset. rs.Filter = adFilterNone

Next, you need a way to solve the conflicts you've found. First of all, you can visit each record in the Recordset and query its Status property. If it returns the value adRecModified, it means that another user has changed the same fields that the current user has edited, whereas it returns the value adRecDeleted if the record has been deleted. Often, the adRecConcurrencyViolation bit is set in case of errors. See Table 13-2 in Chapter 13 for the complete list of values that can be returned by the Status property. Keep in mind that this is a bit-field value, so you should test individual bits using the And operator, as in this code:

If rs.Status And adRecModified Then. . .

If a record has been modified, you must decide what to do. Unfortunately, there are no universally valid rules. Automatic conflict-solving strategies are always dangerous; usually the best thing to do is to let users decide. To let them reach a meaningful decision, however, you should display the new value that has been stored in the database. Unfortunately, simply querying the Field object's UnderlyingValue property won't work because it returns the same value as the OriginalValue property (that is, the value that was in that field when the Recordset was opened). To achieve the correct value for the UnderlyingValue property, you must run the Recordset's Resync method.

You can pass the Resync method two optional arguments. The first argument determines which records are resynchronized, and can be one of the following values: adAffectAllChapters (the default, affect all records), adAffectGroup (affect only the records made visible by the current filter), or adAffectCurrent (affect only the current record). For our purposes, the adAffectGroup value is usually the best choice. The second argument to the Resync method determines how Field object's properties are affected: The value we need is adResyncUnderlyingValues, which sets the UnderlyingValue property to the value read from the database. If you mistakenly use the adResyncAllValues for the second argument (the default), you overwrite the Value property and therefore lose what the user has entered. The following code puts all these concepts together and displays the list of all the conflicting records, with details about the involved fields:

On Error Resume Next rs.UpdateBatch rs.Filter = adFilterConflictingRecords If rs.RecordCount Then Dim fld As ADODB.Field ' Resync the Recordset to retrieve correct values for UnderlyingValue. rs.Resync adAffectGroup, adResyncUnderlyingValues ' Loop on all the conflicting records. Note that setting the Filter ' property implicitly performs a MoveFirst method. Do Until rs.EOF Print "Conflict on record: " & rs("ProductName") For Each fld In rs.Fields ' Display fields whose local and underlying values don't match. If fld.Value <> fld.UnderlyingValue Then Print "Field: " & fld.Name _ & "- Original value = " & fld.OriginalValue _ & "- Value now in database = " & fld.UnderlyingValue _ & "- Local value = " & fld.Value End If Next rs.MoveNext Loop End If rs.Filter = adFilterNone

ADO signals a conflict even if the underlying value is equal to the local value. In other words, ADO signals a conflict if two users attempted to store the same value in the same field of a record. After you or your users have all the information needed to reach a decision, you should resolve the conflict in one of the following ways:

• You can accept the value currently in the database, which you do by assigning the Field's UnderlyingValue property to its Value property.
• You can force the local value into the database, by reexecuting the UpdateBatch method. In this case, no error will be raised unless another user has modified those fields (or other fields) in the meantime.

To watch optimistic batch updates in action, run two instances of the BatchUpd project on the companion CD, modify the same records in both instances, and then click on the Update button. In the first instance, you get an OK message; in the other instance, you get an error and have the opportunity to browse conflicting records, resynchronize the Recordset, and see the relevant properties of all fields, which are visible in Figure 14-2. The application works with the Pubs SQL Server database and with the Biblio.mdb Jet database.

Figure 14-2. The Optimistic Batch Updates demonstration program.

The Update Criteria property

As I've mentioned before, ADO by default raises a conflict when different users modify the same field: In other words, if two users read the same record but modify different fields, no error occurs. This behavior is very dangerous and might lead to inconsistencies in the database. Fortunately, you can change this default action through the Update Criteria dynamic property of the Recordset object. This property affects the fields used by ADO to locate the record to be updated. You can set this property to one of the following four values: 0-adCriteriaKey (ADO uses only the primary key), 1-adCriteriaAllCols (ADO uses all the columns in the Recordset), 2-adCriteriaUpdCols (the default, ADO uses the key and the updated fields), and 3-adCriteriaTimeStamp. (ADO uses a TIMESTAMP column, if available; otherwise, it uses adCriteriaAllCols.)

Because Update Criteria is a dynamic property, you set it through the Properties collection, as in the following example:

rs.Properties("Update Criteria") = adCriteriaTimeStamp

In general, setting this property to the value adCriteriaTimeStamp offers the best performance if the table includes a TIMESTAMP field; otherwise, this setting reverts to adCriteriaAllCols, which is the least efficient of the group (although it's also the safest one). Note that you don't need to retrieve the TIMESTAMP field just to use the adCriteriaTimeStamp setting. For more information, see article Q190727 in the Microsoft Knowledge Base.

We've seen how you can execute commands with a Connection's Execute method and retrieve a set of records using a Recordset's Open method. You can write many useful applications with just those two simple techniques, but you need the power of the Command object for more demanding tasks. Command objects are the best choice when you want to run parameterized queries and are the only viable solution when you want to call stored procedures with parameters and return values.

A Command object represents a command that you intend to perform on a data source. You need an open connection to actually perform the command, but you don't need to have it available when you create the Command object. In other words, you can create a stand-alone Command and set its properties and then associate it with an open Connection object through the Command's ActiveConnection property, which works similarly to the Recordset property of the same name. Here's an example of a simple Command that performs an UPDATE SQL statement on the Publishers table of the Pubs SQL Server's sample database:

' Prepare the Command object's properties. Dim cmd As New ADODB.Command cmd.CommandText = "UPDATE Publishers SET city = 'London' " _ & "WHERE Pub_ID = '9999'" cmd.CommandTimeout = 10 cmd.CommandType = adCmdText ' This saves ADO some work. ' When you're ready, open the connection and fire the command. Dim cn As New ADODB.Connection Dim recs As Long cn.Open "Provider=sqloledb;Data source=p2;user id=sa;initial catalog=pubs" Set cmd.ActiveConnection = cn cmd.Execute recs Print "RecordsAffected = " & recs

Alternatively, you can assign a string to the Command's ActiveConnection property, in which case ADO creates an implicit Connection object for you. I recommend that you not use this latter technique because it gives you little control on the Connection itself—for example, you can't trap events from it—but here's an example of this technique for those of you who like concise code:

cmd.ActiveConnection = "Provider=sqloledb;Data Source=p2;User Id=sa;" _ & "Initial Catalog=pubs" cmd.Execute recs

You can use a Command object to run a row-returning query in three distinct ways. They're equivalent, and you can choose one depending on the particular task or your coding style. In the first technique, you simply assign a Recordset object the return value of a Command's Execute method:

' This code assumes that Command's properties have been set correctly. Dim rs As ADODB.Recordset cmd.CommandText = "SELECT * FROM Publishers WHERE country = 'USA'" Set rs = cmd.Execute ' At this point, the Recordset is already open.

Using the second technique, you assign the Command object to a Recordset's Source property, as in the following code:

Set rs.Source = cmd rs.Open

The third technique is the most concise of the group:

rs.Open cmd

When you pass a Command object to a Recordset's Open method, the Recordset inherits the Command's active connection. For this reason, if you also pass a distinct Connection object or a connection string to the Open method, an error occurs. You also get an error if you pass a Command object that isn't associated with an open connection. After you have associated a Command with a Recordset, you can get a reference to the original Command using the Recordset's ActiveCommand property. Don't try to assign a Command to this property, however, because it's read-only.

In the code you've seen so far, there is no advantage in using Command objects instead of plain SQL commands. The real power of these objects becomes apparent when the command or the query contains one or more parameters. For example, let's say that you often have to select publishers in a given country. This is how you can prepare such a query using a Command object:

Dim cmd As New ADODB.Command, rs As ADODB.Recordset cmd.ActiveConnection = "Provider=sqloledb;Data source=p2;user id=sa;" _ & "initial catalog=pubs" ' Use question marks as placeholders for parameters. cmd.CommandText = "SELECT * FROM Publishers WHERE country = ?" ' You can pass CommandType as the Execute's third argument. Set rs = cmd.Execute(, "USA", adCmdText)

When you create multiple parameters, you must pass their values in an array of Variants, which you can do using an Array function:

cmd.CommandText = "SELECT * FROM Publishers WHERE country = ? " _ & " AND Pub_Name LIKE ?" ' Note that the LIKE operator follows the SQL Server syntax. Set rs = cmd.Execute(, Array("USA", "N%"), adCmdText)

You can write more elegant code if you assign parameters' values through the Parameters collection:

cmd.Parameters.Refresh ' Create the collection (optional). cmd.Parameters(0) = "USA" cmd.Parameters(1) = "N%" Set rs = cmd.Execute()

The Parameters collection's Refresh method is optional because as soon as you reference any property or method of the collection (except Append), ADO parses the query text and builds the collection for you, at the expense of some overhead. Fortunately, it isn't difficult to create the collection yourself and save this overhead, using the Command's CreateParameter method:

' Create the collection of parameters. (Do this only once.) With cmd.Parameters .Append cmd.CreateParameter("Country", adChar, adParamInput, 20) .Append cmd.CreateParameter("Name", adChar, adParamInput, 20) End With ' Assign a value to parameters. cmd.Parameters("Country") = "USA" cmd.Parameters("Name") = "N%" Set rs = cmd.Execute()

The Command object's Prepared property plays a key role in optimizing your parameterized queries. If this property is True, ADO creates a temporary stored procedure on the server the first time you invoke the Execute method of the Command object. This adds a little overhead to the first execution, but it noticeably speeds up all subsequent calls. The temporary stored procedure is automatically deleted when the connection is closed. One note: By tracing SQL calls, I found that this property doesn't work very well with SQL Server 6.5 SP3 or earlier.

You can greatly simplify your coding by using Connection and Command objects defined at design time through the DataEnvironment designer. As you'll see in a moment, the amount of necessary code is dramatically reduced because most of the properties of these objects can be set interactively at design time, using a RAD approach that isn't conceptually different from what you routinely do with forms and controls.

You can use a reference to a Connection object to open a database, start a transaction, and so on. In many cases, however, you don't even need to explicitly open a connection because the run-time instance of the DataEnvironment will do it for you whenever you reference a Command object that's a child of that connection. In practice, you reference a Connection only if you need to set some of its properties—for example, the user name and the password:

' This code assumes that Data Environment1 has a Connection object named "Pubs" ' and a Command object named ResetSalesReport under it. Dim de As New DataEnvironment1 de.Pubs.Open userid:="sa", Password:="mypwd" de.ResetSalesReport

Remember that you can decide whether a login dialog box is shown to the user by setting the RunPromptBehavior property appropriately. All the Command objects you have defined at design time become methods of the DataEnviromment. The following code example directly executes the Command object, without first explicitly opening the Connection because all the login information has been specified at design time:

' You can hardly write code more concise than this! DataEnvironment1.ResetSalesReport

The previous two code snippets differ significantly in this respect: The former explicitly creates an instance—named de—of the DataEnvironment1 designer, while the latter uses its global name. It turns out that Visual Basic manages DataEnvironment designers a bit like form designers, in that you can use the class name as a variable. (This detail is covered in Chapter 9.) Keep this in mind because you might accidentally create more instances of the designer without realizing that you're wasting memory and resources.

At run-time, the DataEnvironment designer object exposes three collections: Connections, Commands, and Recordsets. You can use them to let your users select the query they want to run against the database:

' Fill a list box with the names of all supported Commands. ' BEWARE: just referencing the Commands collection opens the connection. Dim cmd As ADODB.Command For Each cmd In DataEnvironment1.Commands List1.AddItem cmd.Name Next

An instance of the designer exposes a collection of Connections and Commands and also exposes one Recordset object for each Command that returns a result set. The name of this Recordset is formed using the rs prefix followed by the name of the Command that generates it. For example, if you have defined a Command object named Authors that performs a query, the DataEnvironment object will also expose a property named rsAuthors of type Recordset. By default, this Recordset is closed, so before using it you need to run the associated Command:

' Fill a list box with authors' names. Dim de As New DataEnvironment1 de.Authors ' Run the query. Do Until de.rsAuthors.EOF List1.AddItem de.rsAuthors("au_fname") & " " & de.rsAuthors("au_lname") de.rsAuthors.MoveNext Loop de.rsAuthors.Close

Alternatively, you can explicitly open the Recordset object. This latter technique is more flexible because you can set the Recordset's properties before opening it:

Dim rs As ADODB.Recordset ' Get a reference to the Recordset, and open it with an optimistic lock. Set rs = DataEnvironment1.rsAuthors rs.Open LockType:=adLockOptimistic Do Until rs.EOF List1.AddItem rs("au_fname") & " " & rs("au_lname") rs.MoveNext Loop rs.Close

Of course, you can declare the rs variable using the WithEvents keyword so that you can trap all the events raised by the Recordset object.

If a Command object expects one or more parameters, you can just pass them after the Command name. To test this feature, create a Command object named AuthorsByState under a Connection object to the Pubs SQL Server database, based on the following query:

SELECT au_lname, au_fname, address, city, zip, state FROM authors WHERE (state =?)

and then run this code:

DataEnvironment1.AuthorsByState "CA" ' Show the results in a DataGrid control. Set DataGrid1.DataSource = DataEnvironment1.rsAuthorsByState

Things are more complex when you're running a parameterized stored procedure because ADO sometimes is unable to determine the right type for its parameters, and you probably have to adjust what the DataEnvironment designer displays in the Parameters tab of the Command object's Property Pages dialog box. Also, if you're working with SQL Server 6.5, be sure that you've installed its Service Pack 4 (which you can find on the Visual Studio CD), which has fixed several problems in this area. Let's say that you need to call an SQL Server stored procedure named SampleStoredProc, which takes one input parameter and one output parameter and has a return value. This is what the Visual Basic documentation suggests:

Dim outParam As Long, retValue As Long retValue = DataEnvironment1.SampleStoredProc(100, outParam) Set DataGrid1.DataSource = DataEnvironment1.rsSampleStoredProc Print "Output parameter = " & outParam Print "Return value = " & retValue

I found many problems using this syntax. Even worse, you can't use this approach when you want to omit one or more parameters. To work around these problems, you can resort to the Parameters collection of the ADO Command object. To get a reference to this object, you must query the DataEnvironment's Commands property, as in the following piece of code:

With DataEnvironment1.Commands("SampleStoredProc") ' This is the "royalty" parameter. .Parameters(1) = 100 Set DataGrid1.DataSource = .Execute ' Retrieve the output parameter. Print "Output parameter = " & .Parameters(2) ' The return value is always in Parameters(0). Print "Return value = " & .Parameters(0) End With

An important point: When you use the Commands collection to retrieve the ADO Command object, you're in a sense bypassing the Recordset-returning mechanism offered by the DataEnvironment designer. For this reason, you can retrieve the Recordset only by reading the return value of the Execute method, and you can't rely on the rsSampleStoredProc property of the designer. Finally, you can also pass input parameters directly to the Execute method and retrieve output parameters and return values using the Parameters collection:

Dim recordsAffected As Long With DataEnvironment1.Commands("SampleStoredProc") ' The array of parameters passed to the Execute method must account for ' the return value, which is always the first parameter. Set DataGrid1.DataSource = .Execute(recordsAffected, Array(0, 100)) Print "Output parameter = " & .Parameters(2) Print "Return value = " & .Parameters(0) End With

Up to this point, I've illustrated the virtues of the DataEnvironment designer to create Connection, Command, and Recordset objects that you can use from code without having to define them at run time. However, you shouldn't forget that you can also write code inside the designer itself. This code might respond to events raised by the Connection and Recordset objects created by the DataEnvironment itself. In addition, you can add public properties, methods, and events as you can do with any type of class module. These capabilities let you encapsulate some complex programming logic inside a DataEnvironment module and reuse it in many other applications.

One possible use for such public properties is to offer meaningful names for the parameters that you should pass to a Command's Parameters collection, as in the following code:

' Inside the DataEnvironment module Public Property Get StateWanted() As String StateWanted = Commands("AuthorsByState").Parameters("State") End Property Public Property Let StateWanted(ByVal newValue As String) Commands("AuthorsByState").Parameters("State") = newValue End Property

Here's another example—a property named InfoText, which gathers all the output coming from the Connection's InfoMessage event:

Private m_InfoText As String Public Property Get InfoText() As String InfoText = m_InfoText End Property Public Property Let InfoText(ByVal newValue As String) m_InfoText = newValue End Property ' Add a new text line to the InfoText property. Private Sub Connection1_InfoMessage(ByVal pError As ADODB.Error, _ adStatus As EventStatusEnum, ByVal pConnection As ADODB.Connection) m_InfoText = m_InfoText & "pError = " & pError.Number & " - " & _ pError.Description & vbCrLf End Sub

I was thrilled when I first saw the DataEnvironment object in action, and I think I have expressed my enthusiasm many times in these pages. But it wouldn't be fair if I failed to mention that the DataEnvironment designer still has a few serious problems, which sometimes prevent it from being used in production applications. Here's a brief list of my disappointing discoveries:

• The DataEnvironment isn't very good at dealing with parameterized stored procedures, especially those that take output parameters and return a value. In particular, sometimes you can't call these procedure using the Command's name as a method of the DataEnvironment object—as in the DataEnvironment1.SampleStoredProc example I showed you previously—and you're forced to pass parameters through the Command's Parameters collection.
• When you use the DataEnvironment designer as a data source for one or more bound controls, you can't rely on the automatic connection mechanism provided by the DataEnvironment. In fact, if the connection fails, no error is returned to the program: You'll just find no data in bound controls and won't be given a clue about what went wrong. If the Connection object's RunPromptBehavior property is set to adPromptNever (the preferred setting for most real-world applications), your users have no way to correct the problem. For this reason, you should always test to see whether the connection is open in the Form_Load event procedure, as in this code:

Private Sub Form_Load() If (DataEnv1.Connection1.State And adStateOpen) = 0 Then ' In a real application you'll do something smarter ' than just showing a message box. MsgBox "Unable to open the connection", vbCritical End If End Sub

• In general, you can't be sure that the data source path entered at design time will match your users' directory structures. So you must provide a means of configuring the application with the correct path—by reading it from a Registry or an INI file, for example—and then enforcing it before showing data coming from the database, as in the following code:

Private Sub Form_Load() If (DataEnv1.Connection1.State And adStateOpen) = 0 Then Dim conn As String conn = "Provider=Microsoft.Jet.OLEDB.3.51;"_ & "Data Source=???" ' ReadDataBasePathFromINI is defined elsewhere. conn = Replace(conn, "???", ReadDataBasePathFromINI()) DataEnv1.Connection1.ConnectionString = conn DataEnv1.Connection1.Open End If End Sub

• Under some circumstances, the DataEnvironment object opens more connections than it really needs. For example, if you have two or more instances of the same DataEnvironment designer, each instance opens a distinct connection. If you don't pay attention to this behavior, you might easily consume all the available connections, especially if you're working with SQL Server Developer Edition (which allows a lower number of connections than the "real" product).
• While the DataEnvironment designer behaves like a class module in most regards, its implementation shows a few dangerous quirks. For example, I found that if you use the global DataEnvironment instance to implicitly open a Connection, that connection is never closed while the program is executing. More precisely, a trip to the SQL Server trace confirms that the connection is indeed closed, but this seems to happen after the program has already terminated its execution. It's a minor detail, but it implies that you can't rely on the Connection's Disconnect event to execute your cleanup code. Even more inexplicable is this: The global DataEnviroment instance doesn't even receive a Terminate event, as all objects do when the application terminates, so you can't count on this event to close the connection in an orderly way. This bug manifests itself both in the IDE and in compiled programs.

The bottom line is this: Don't blindly assume that the DataEnvironment designer will work as expected, and always test its behavior in "extreme" conditions, such as when connections aren't guaranteed or are scarce.